package com.onemore.task.model;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.voovan.db.JdbcOperate;
import com.onemore.task.JdbcGlobal;

public class ModelOneWmsTzd extends JdbcGlobal{
	final static private String fields[] = new String[] {"SyncBillId","BILLOUTDATE","QTYIN","SL"};

	static public boolean isNotExist()
	{
		return validateTableExist("ONE_WMS_TZD");
	}
    static public void createTable() throws SQLException
    {
    	String sql = "CREATE TABLE [dbo].[ONE_WMS_TZD] (\r\n" + 
    			"[BillId] varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL DEFAULT '' ,\r\n" + 
    			"[syncbillid] varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL DEFAULT '' ,\r\n" + 
    			"[billdate] char(30) COLLATE Chinese_PRC_CI_AS NOT NULL DEFAULT '' ,\r\n" + 
    			"[QTYIN] int NOT NULL DEFAULT ((0)) ,\r\n" + 
    			")";
//		"PRIMARY KEY ([BillId])\r\n" + 
    	getJdbcOperate().update(sql);
    }
    static public void arrayToTable(List<Map<String, Object>> listWms,String tableName) throws SQLException
	{
		JdbcOperate operate = getJdbcOperate();
		operate.update("truncate table "+tableName);
		for(Map<String, Object> map:listWms) {
			String billId = (String)map.get("BillId");
			String syncbillid = (String)map.get("syncbillid");
			String billdate = (String)map.get("billdate");
			Integer QTYIN = (Integer)map.get("QTYIN");
			operate.update("INSERT INTO "+tableName+" (BillId,syncbillid,billdate,QTYIN)  values('"+billId+"','"+syncbillid+"','"+billdate+"','"+QTYIN+"')");
		}
	}
	static public void importWmsTableTZD() throws SQLException
	{
		arrayToTable(getWmsListFromErp(),"ONE_WMS_TZD");
	}
    static public List<Map<String, Object>> getWmsListFromErp() throws SQLException
    {
    	String sql = "select A.BillId,A.syncbillid,A.billdate,SUM(B.QtyIn)as QTYIN from TBillNotePurchase C, TBillStockPurchase A,TBillStockPurchaseDet B WHERE A.BillId=B.BillId and A.OrigBillId=c.BillId AND A.billdate>='2018-01-01' and A.BillType='0' GROUP BY A.BillId,A.SyncBillId,A.BillDate \r\n" + 
    			" union all select A.BillId,A.syncbillid,A.billdate,SUM(B.QtyOut)as QTYIN from TBillStockPurchase A,TBillStockPurchaseDet B WHERE A.BillId=B.BillId AND A.billdate>'2018-01-01' and A.BillType='1' GROUP BY A.BillId,A.SyncBillId,A.BillDate \r\n" + 
    			" union all select A.syncbillid as BillId,A.BillId as syncbillid,A.billdate,SUM(B.QtyOut)as QTYIN from TBillNoteBatchOut C, TBillBatchOut A,TBillBatchOutDet B WHERE A.BillId=B.BillId and A.OrigBillId=c.BillId AND A.billdate>='2018-01-01' and A.BillType='1'  and A.SyncBillId not like 'PF%' GROUP BY A.BillId,A.SyncBillId,A.BillDate \r\n" + 
    			" union all select A.BillId,A.syncbillid,A.billdate,SUM(B.QtyOut)as QTYIN from TBillBatchOut A,TBillBatchOutDet B WHERE A.BillId=B.BillId AND A.billdate>='2018-01-01' and A.BillType='0' and A.SyncBillId not like 'PF%' GROUP BY A.BillId,A.SyncBillId,A.BillDate";
    	List<Map<String, Object>> manyMaps = null;
		manyMaps = getWmsOperate().queryMapList(sql);
		return manyMaps;
    }
}
